﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using OestsCommon.Model;

namespace OestsDataBase
{
    /// <summary>
    /// 实训组操作
    /// </summary>
    public static class TrainGroupDAL
    {

        //项目表 对应实训 考试
        public static void CreateTable()
        {
            string sqlCheck = "if object_id( 'T_TrainGroups') is not null select 1 else select 0";
            if ((int)SqlHelper.ExecuteScalar(sqlCheck) == 0)
            {
                string sqlCreacte = @"CREATE TABLE T_TrainGroups (id bigint identity(1,1),
                                    name NVARCHAR(max), project_id bigint not null,teacher_id bigint not null)";
                SqlHelper.ExecuteNonQuery(sqlCreacte);
            }
        }
        /// <summary>
        /// 获得所有的数据
        /// </summary>
        /// <returns></returns>
        public static List<TrainGroupModel> GetData()
        {
            string strSql = "select * from T_TrainGroups";
            return GetData(strSql);
        }

        /// <summary>
        /// 获取指定老师管理的实训组
        /// </summary>
        /// <param name="teacherId"></param>
        /// <returns></returns>
        public static List<TrainGroupModel> GetDataByTeacherId(int teacherId)
        {
            string strSql = "select * from T_TrainGroups where teacher_id = " + teacherId;
            return GetData(strSql);

        }

        private static List<TrainGroupModel> GetData(string sql)
        {
            DataTable dt = SqlHelper.ExecuteDataTable(sql);
            if (dt == null) { return null; }
            List<TrainGroupModel> list = new List<TrainGroupModel>();
            foreach (DataRow dr in dt.Rows)
            {
                TrainGroupModel s =    ReadDataFromTrainGroups(dr);
                list.Add(s);
            }
            return list;
        }

        public static TrainGroupModel GetTrainGroup(int groupId)
        {
            string strSql = "select * from T_TrainGroups where id = " + groupId;
            DataTable dt = SqlHelper.ExecuteDataTable(strSql);
            if (dt == null || dt.Rows.Count == 0) { return null; }
            DataRow dr = dt.Rows[0];
            return ReadDataFromTrainGroups(dr);
        }

        private static TrainGroupModel ReadDataFromTrainGroups(DataRow dr)
        {
            TrainGroupModel s = new TrainGroupModel();
            s.Id = Int32.Parse(dr["id"].ToString());
            s.Name = dr["name"].ToString();
            s.ProjectId = Int32.Parse(dr["project_id"].ToString());
            s.TeacherId = Int32.Parse(dr["teacher_id"].ToString());
            s.Tasks = GetTasksByTaskGroupId(s.ProjectId, s.Id);
            return s;
        }

        private static List<TaskModel> GetTasksByTaskGroupId(int projectId,int groupId)
        {
            List<TaskModel> list = new List<TaskModel>();

            string strSql = "select * from T_TrainGroup_" + groupId;

            DataTable dt = SqlHelper.ExecuteDataTable(strSql);
            if (dt == null) { return null; }
            foreach (DataRow dr in dt.Rows)
            {
                int id = Int32.Parse(dr["task_id"].ToString());
                int time = dr["time"].Equals(DBNull.Value) ? 0 : Int32.Parse(dr["time"].ToString());
                TaskModel t = TaskDAL.GetTaskById(projectId, id);
                if (t != null)
                {
                    t.SetTime = time;
                    list.Add(t);
                }

            }
            return list;
        }

        public static int AddTrainGroup(string name, int projectId, int teacherId)
        {
            if (name == "") return -1;
            string strSql = string.Format(@"insert into T_TrainGroups (name,project_id,teacher_id)values('{0}',{1},{2});SELECT @@Identity", name, projectId, teacherId);
            object obj = SqlHelper.ExecuteScalar(strSql);

            int nID = Int32.Parse(obj.ToString());
            //创建一个实训组的表
            CreatTaskGroup(nID);
            return nID;
        }

        /// <summary>
        /// 创建实训组的表
        /// </summary>
        /// <param name="id"></param>
        private static void CreatTaskGroup(int id)
        {
            string sqlCheck = string.Format("if object_id('{0}') is not null select 1 else select 0", "T_TrainGroup_" + id);
            if ((int)SqlHelper.ExecuteScalar(sqlCheck) == 0)
            {
                string sqlCreate = @"CREATE TABLE T_TrainGroup_" + id + @" (task_id bigint  primary key, time int)";
                SqlHelper.ExecuteNonQuery(sqlCreate);
            }
            else
            {
                //清空已有数据，并且将自增自段恢复从1开始计数 
                string sql = "truncate table T_TrainGroup_" + id;
                SqlHelper.ExecuteNonQuery(sql);
            }
        }

        /// <summary>
        /// 修改 
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public static bool ModifyTrainName(int id, string trainName = null)
        {
            object obj;
            if (trainName == null)
                obj = DBNull.Value;
            else
                obj = trainName;
            string strSql = string.Format("update T_TrainGroups set name='{0}' where id = @id", obj);
            int n = SqlHelper.ExecuteNonQuery(strSql, new SqlParameter("@id", id));
            if (n != 0)
            {
                return true;
            }
            return false;
        }


        /// <summary>
        /// 删除实训组  
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public static bool DelTrainGroup(int id)
        {
            string strSql = string.Format("delete from T_TrainGroups  where id = @id");
            int n = SqlHelper.ExecuteNonQuery(strSql, new SqlParameter("@id", id));
            if (n != 0)
            {
                // 删除对应表
                string sqlCheck = string.Format("if object_id('{0}') is not null select 1 else select 0", "T_TrainGroup_" + id);
                if ((int)SqlHelper.ExecuteScalar(sqlCheck) == 1)
                {
                    string sqlCreate = @"drop TABLE T_TrainGroup_" + id;
                    SqlHelper.ExecuteNonQuery(sqlCreate);
                }
                //检查表是否为空，为空后重置自增字段 暂不确定是否需要

                return true;
            }
            return false;
        }

        /// <summary>
        /// 修改
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public static bool SetTrainTime(int trainGroupId, int TaskId, int time)
        {
            string strSql = string.Format("update T_TrainGroup_" + trainGroupId + " set time = {0} where task_id = @id", time);
            int n = SqlHelper.ExecuteNonQuery(strSql, new SqlParameter("@id", TaskId));
            if (n != 0)
            {
                return true;
            }
            return false;
        }


        public static bool SetTrainSettings(int id, int time)
        {
            return false;
        }


        public static void AddTaskToTrainGroup(int trainGroupId, int taskId, int time)
        {
            string strSql = string.Format(@"insert into T_TrainGroup_" + trainGroupId + " (task_id,time)values({0},{1})", taskId, time);
            SqlHelper.ExecuteScalar(strSql);
        }

        public static bool DelTaskFromTrainGroup(int trainGroupId, int taskId)
        {
            string strSql = string.Format("delete from  T_TrainGroup_" + trainGroupId + "  where task_id = @id");
            int n = SqlHelper.ExecuteNonQuery(strSql, new SqlParameter("@id", taskId));
            if (n != 0)
            {
                return true;
            }
            return false;
        }

    }
}
